﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using NetDiskPRJ.DBUtility;
using System.Collections.Generic;//Please add references
namespace NetDiskPRJ.DAL
{
    /// <summary>
    /// 通用类
    /// </summary>
    public class Common
    {
        public Common()
        { }

        /// <summary>
        /// 根据sql语句查询数据
        /// <creator>sunlj</creator>
        /// <date>2013-06-27</date>
        /// </summary>
        /// <param name="SQLSyntax"></param>
        /// <returns></returns>
        public DataSet getListBySql(string SQLSyntax)
        {
            return DbHelperSQL.Query(SQLSyntax);
        }

        //委托
        public delegate void dDownloadProgress(long total, long current);
        //事件
        public event dDownloadProgress onDownLoadProgress;
        //开始模拟工作
        public void Start()
        {
            for (int i = 0; i < 100; i++)
            {
                if (onDownLoadProgress != null)
                    onDownLoadProgress(100, i);
                System.Threading.Thread.Sleep(100);
            }
        }


        /// <summary>
        /// 根据EDI文件更新数据库
        /// <creator>sunlj</creator>
        /// <date>2013-6-28</date>
        /// </summary>
        /// <param name="rows"></param>
        public void UpdateByEDI(DataRow[] rows,string filePath,int outTimes=0)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> commandInfoList = new List<CommandInfo>();
            CommandInfo commandInfo = new CommandInfo();
            SqlParameter[] parameters = new SqlParameter[] { };

            SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = outTimes;
            

            NetDiskPRJ.Common.GlobalUtil.ProgressStepCount = 0;//进度条的完成度

            try
            {
                foreach (DataRow row in rows)
                {
                    string tableName = row["tableName"].ToString().Trim();
                    string fileName = tableName + ".xml";
                    string keyField = row["KEYField"].ToString().Trim();
                    string identity = row["identity"].ToString().Trim();
                    string trigger = row["trigger"].ToString().Trim();
                    DataSet ds = new DataSet();
                    ds.ReadXml(filePath + @"\" + fileName, XmlReadMode.ReadSchema);



                    if (trigger == "on")
                    {
                        //关闭触发器
                        strSql = new StringBuilder();
                        strSql.Append("alter table " + tableName + " disable trigger all ");
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();
                    }

                    if (keyField.Trim() == "")
                    {
                        strSql = new StringBuilder();
                        strSql.Append("TRUNCATE TABLE " + tableName);
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();

                        //使用sqlbulkcopy导入xml到sql
                        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                        sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名
                        sqlbulkcopy.BulkCopyTimeout = outTimes;
                        sqlbulkcopy.WriteToServer(ds.Tables[0]);
                    }
                    else
                    {
                        //生成临时数据库
                        strSql = new StringBuilder();
                        strSql.Append("SELECT  * ");
                        strSql.Append(" INTO    #" + tableName);
                        strSql.Append(" FROM  " + tableName);
                        strSql.Append(" WHERE   0 = 1");
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();

                        //使用sqlbulkcopy导入xml到 临时表里
                        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                        sqlbulkcopy.DestinationTableName = "#"+tableName;//数据库中的表名
                        sqlbulkcopy.BulkCopyTimeout = outTimes;
                        sqlbulkcopy.WriteToServer(ds.Tables[0]);

                        //分离keyField.Trim()
                        string[] keyFieldList = keyField.Trim().Split(',');
                        //删除临时表里有的数据
                        strSql = new StringBuilder();
                        strSql.Append(" DELETE  FROM " + tableName);
                        strSql.Append(" WHERE   EXISTS ( SELECT * ");
                        strSql.Append(" FROM   #" + tableName + " WHERE 1=1 ");

                        for (int i = 0; i < keyFieldList.Length; i++)
                        {
                            strSql.Append(" AND " + keyFieldList[i] + "=" + tableName + "." + keyFieldList[i]);
                        }

                        strSql.Append(" ) ");
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();

                        //把临时表的数据插入到数据库中
                        strSql = new StringBuilder();
                        if (identity == "on")
                        {
                            //关闭约束
                            strSql.Append("set IDENTITY_INSERT " + tableName + " on;");
                        }
                        strSql.Append(" INSERT  INTO  " + tableName);
                        strSql.Append(" SELECT  * ");
                        strSql.Append(" FROM    #" + tableName);
                        if (identity == "on")
                        {
                            //开启约束
                            strSql.Append("set IDENTITY_INSERT " + tableName + " off;");
                        }
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();

                        //删除临时表
                        strSql = new StringBuilder();
                        strSql.Append(" DROP TABLE #" + tableName);
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();
                    }

                    if (trigger == "on")
                    {
                        //开启触发器
                        strSql = new StringBuilder();
                        strSql.Append("alter table " + tableName + " enable trigger all ");
                        command = new SqlCommand(strSql.ToString(), conn, tran);
                        command.CommandTimeout = outTimes;
                        command.ExecuteNonQuery();
                    }

                    
                    NetDiskPRJ.Common.GlobalUtil.ProgressStepCount++;
                }
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

